if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_saveLeave')
          and type in ('P','PC'))
   drop procedure bd_proc_saveLeave
go


create procedure bd_proc_saveLeave (@startDate date,@endDate date,@description VARCHAR (2000),@totalDuration DECIMAL,@orderNo VARCHAR(10),@leaveId VARCHAR(20),@operator VARCHAR(20),@orderId NUMERIC,@ishaveedu NUMERIC,@applyType NUMERIC) as
BEGIN
	DECLARE @user_serial NUMERIC
	DECLARE @dept_serial numeric --本年福利固定不变
	DECLARE @dept_name VARCHAR(100) --临时数据
	DECLARE @count INT
	DECLARE @totalCount int
	--假类信息
	--DECLARE @ishaveedu int --是否是额度假，并且0或null表示不是额度假，1表示调休，2表示年假
	DECLARE @usable_time numeric --可用时间量（分钟）
	DECLARE @lastnj numeric --上年年假
	DECLARE @lastfl numeric --上年福利
	DECLARE @nj numeric --本年年假
	DECLARE @fl numeric --本年福利
	DECLARE @old_lastnj numeric --上年年假固定不变
	DECLARE @old_lastfl numeric --上年福利固定不变
	DECLARE @old_nj numeric --本年年假固定不变
	DECLARE @old_fl numeric --本年福利固定不变
	DECLARE @temp_time numeric = 0 --临时数据
	DECLARE @status numeric --单子审批状态
	DECLARE @njcount numeric --年假总数（非剩余总数）
	DECLARE @txcount numeric --调休总数（非剩余总数）

	DECLARE @ho_year int
	DECLARE @ho_month int

	set @ho_year = YEAR(GETDATE())
	set @ho_month = MONTH(GETDATE())

	DECLARE @firstDay varchar(10) = concat(year(GETDATE()),'-01-01')
	DECLARE @fdClearDay varchar(10)

	select top 1 @fdClearDay = concat(year(GETDATE()),SUBSTRING(convert(varchar(10),fd_nj_clear_date,120), 5, 6)) from wt_reg;

	DECLARE @isOK int=0 --用于判断假期额度是否足够 0足够  1不足

	begin try
		begin tran
			--创建 返回不满足请假的人的原因
			create table #return_table(
				user_serial numeric not null ,
				user_no VARCHAR(64) COLLATE Chinese_PRC_CI_AS not NULL,
				user_name VARCHAR(64) COLLATE Chinese_PRC_CI_AS not NULL,
				dept_name VARCHAR(64) COLLATE Chinese_PRC_CI_AS not NULL,
				error_msg VARCHAR(200) COLLATE Chinese_PRC_CI_AS not NULL
			)
			--创建可提交的人员列表
			select * into #qualified_people from bd_temp_checkPeople where 1=2;
			--定义游标
			--声明一个游标userOfLeave
			DECLARE userOfLeave CURSOR LOCAL FOR SELECT
				b.user_serial,
				u.user_dep as dept_serial,
				b.dept_name
			FROM
				bd_temp_checkPeople b
				INNER JOIN dt_user u on u.user_serial = b.user_serial
			WHERE order_id = @orderId

			OPEN userOfLeave FETCH NEXT FROM userOfLeave INTO @user_serial,@dept_serial,@dept_name--提取第一条数据存放在变量中
			WHILE @@fetch_status = 0
				BEGIN
				--重置@totalCount
					set @totalCount = 0;--处理请假日期中包含在封账日期内，过滤掉，提示：部分请假时间包含在封账时段内，这个和另外（下面的）两种不符合条件独立出来，这里不符合，就不去验证其他。


					select @count = count(1) from bd_kq_accounts a
					INNER JOIN (
					SELECT
						@user_serial as userSerial,*
					FROM
						( SELECT YEAR ( CONVERT ( DATE, leave_date )) AS leaveyear, MONTH ( CONVERT ( DATE, leave_date )) AS leavemonth FROM bd_temp_dayDetail where order_id = @orderId ) b
					GROUP BY
						b.leaveyear,
						b.leavemonth) c on a.user_serial = c.userSerial and a.year = c. leaveyear and a.month = c.leavemonth
				--更新@totalCount
					set @totalCount = @count;
				if (@count>0)
					BEGIN
						--请假的日期中有已封账的日期
						insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
								SELECT
									u.user_serial,
									u.user_no,
									u.user_lname,
									d.dep_name,
									'部分请假时间包含在封账时段内'
								FROM
									dt_user u
									INNER JOIN dt_dep d ON u.user_dep = d.dep_serial
									where u.user_serial = @user_serial
					END
					--不存在封账的日期
					ELSE BEGIN
						--1、请假人的相同请假日期是否存在相同的假类（和已请过的假单明细对比）
						select
							@count = count(1)
						from bd_temp_checkPeople p
						INNER JOIN lr_kqingj l on l.user_serial = p.user_serial
						INNER JOIN bd_temp_dayDetail d on d.leave_date = CONVERT(varchar(10),l.kssj,120)
						INNER JOIN bd_leave_order o on o.order_id = l.order_id
						where p.user_serial = @user_serial and p.order_id = @orderId and d.order_id = @orderId
						and l.bh = @leaveId
						and o.status!=2
						and o.is_del!=-1
						and l.whether_validity!=2;
						--更新@totalCount
						set @totalCount = @count;
						--@count>0表示本次请假日期，和已请假有冲突
						if(@count>0)
							BEGIN
								insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
								SELECT
									u.user_serial,
									u.user_no,
									u.user_lname,
									d.dep_name,
									'请假类型冲突(同天不能同假)'
								FROM
									dt_user u
									INNER JOIN dt_dep d ON u.user_dep = d.dep_serial
									where u.user_serial = @user_serial
							END
						--2、请假人假类额度是否充足（本次请假的天数*每天请假的时长要小于可用额度）
						--判断该假有额度功能,这里先设固定值，之后修改为查询获得
						--select @ishaveedu = isnull(ishaveedu,0) from kt_qingj where bh = @leaveId;
						--
						if @ishaveedu!=0
						BEGIN
							if @ishaveedu=1
							BEGIN
								select @usable_time =txts,@txcount = txcount from bd_nianjia where user_serial = @user_serial;
								if(@totalDuration>@usable_time)
									BEGIN
										set @isOK = 1;
									END
							END
							ELSE IF @ishaveedu=2
							BEGIN
							--逻辑变更：根据实际请假的日期归属的范围，来计算范围内可用的值（一个请假单多个日期的跨度）。
													--日期都是以申请时所在的年
													--1月1日之前的部分，只能使用上年年假
													--1月1日到3月1日之间的部分，都可以使用
													--3月1日之后的部分，只能使用当年的假期
							--可以以此来判断年假额度是否足够，（不足则移除人员，足，则本次计算的各个账户剩余量即可作为已扣除之后的余额来使用）

								select @lastnj = lastfdnj,
												@nj =fdnjts,
												@fl = flnjts,
												@njcount = njcount
								from bd_nianjia where user_serial = @user_serial;

								set	@old_lastnj = @lastnj
								set @old_nj = @nj
								set @old_fl = @fl

								--1月1日之前的部分，只能使用上年年假  @firstDay  @fdClearDay
								select @temp_time = isnull(sum(leave_minute),0)
								from bd_temp_dayDetail
								where order_id = @orderId and leave_date < @firstDay;

								if @temp_time <= @old_lastnj
									BEGIN
										set @lastnj = @lastnj - @temp_time;
										--1月1日到3月1日之间的部分，都可以使用
										select @temp_time = isnull(sum(leave_minute),0)
										from bd_temp_dayDetail
										where order_id = @orderId and leave_date >= @firstDay and leave_date<@fdClearDay;

										if @temp_time <= @lastnj+@nj+@fl
											BEGIN
												--按顺序扣除  上年年假--本年年假---本年福利
												if @lastnj < @temp_time
													BEGIN
														set @temp_time = @temp_time - @lastnj

														if @nj < @temp_time
															BEGIN
																set @temp_time = @temp_time - @nj
																set @nj = 0
																set @fl = @fl - @temp_time
															END
														else
															BEGIN
																set @nj = @nj - @temp_time
															END
													END

												--3月1日之后的部分，只能使用当年的假期
												select @temp_time = isnull(sum(leave_minute),0)
												from bd_temp_dayDetail
												where order_id = @orderId and leave_date >= @fdClearDay;

												if @temp_time > @nj+@fl
													BEGIN
														set @isOK = 1;
													END
											END
										else
											BEGIN
												set @isOK = 1;
											END
									END
								ELSE
									BEGIN
											set @isOK = 1;
									END
							END

							if @isOK = 1
								BEGIN
									--积累
									set @totalCount = @totalCount+1;
									--判断临时表#return_table中是否有这个人的信息，有则修改，无则添加
									select @count = count(1) from #return_table where user_serial = @user_serial;
									if(@count>0)
										BEGIN
											update #return_table set error_msg = '1.'+error_msg+'，2.假类可用额度不足' where user_serial = @user_serial;
										END
										ELSE BEGIN
											insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
											SELECT
												u.user_serial,
												u.user_no,
												u.user_lname,
												d.dep_name,
												'假类可用额度不足'
											FROM
												dt_user u
												INNER JOIN dt_dep d ON u.user_dep = d.dep_serial
												where u.user_serial = @user_serial
										END
								END
						END

					END

					--合格的人则放入#qualified_people
					--新逻辑：重构bd_kq_holidays表，
					--按人按天按类型（请年假、请调休、福利补贴、福利清零、加班工时确认等，年假调整）储存数据
					--请假的取消、驳回、通过，
					--加班的确认加班工时，确认加班工时之后的加班取消、修改确认加班工时
					--年假的调整（年初）
					--福利假的清零、录入
					--额度每日扣除

					if @totalCount = 0
						BEGIN

							insert into #qualified_people
							select  * from bd_temp_checkPeople where user_serial = @user_serial and order_id = @orderId;
-- 							--bd_nianjia冻结,1冻结调休，2冻结年假
-- 							if @ishaveedu=1
-- 							BEGIN
-- 								--冻结调休
-- 								update bd_nianjia
-- 									set sdtx = isnull(sdtx,0) + @totalDuration
-- 									where user_serial = @user_serial
--
-- 							END
-- 							ELSE IF @ishaveedu=2
-- 							BEGIN
-- 								--冻结年假
-- 								--扣除顺序：上年法定年假-->上年福利年假-->本年法定年假-->本年福利年假
-- 								update bd_nianjia
-- 									set sdnj = isnull(sdnj,0) + @totalDuration
-- 									where user_serial = @user_serial
--
-- 							END
						END
					FETCH NEXT FROM userOfLeave INTO @user_serial,@dept_serial,@dept_name
				END

			--根据#qualified_people中是否有数据，来判断是否有人不满足请假条件，有满足，则保存请假单和请假明细
			select @count = count(1) from #qualified_people;
			if(@count>0)
				BEGIN

					DECLARE @ho_type int = 0 ;
					if @ishaveedu=1
						BEGIN
						 --按人按天，类型为21   插入bd_kq_holidays
							set @ho_type = 21
						END
					ELSE IF @ishaveedu=2
						BEGIN
							--按人按天，类型为1   插入bd_kq_holidays
								set  @ho_type = 1
						END
					if @ho_type != 0
						begin
							--插入到bd_kq_holidays表
							insert into bd_kq_holidays(
											user_serial,
											dept_serial,
											dept_name,
											ho_year,
											ho_month,
											ho_date,
											ho_type,
											use_time,
											related_id,
											create_by)
								select
										p.user_serial,
										dep.dep_serial,
										dep.dep_name,
										DATEPART(year,d.leave_date),
										DATEPART(month,d.leave_date),
										d.leave_date,
										@ho_type,
										d.leave_minute,
										@orderId,
										@operator
								FROM
									bd_temp_dayDetail d
									INNER JOIN #qualified_people p on d.order_id = p.order_id
									INNER JOIN dt_user u on u.user_serial = p.user_serial
									INNER JOIN dt_dep dep on dep.dep_serial = u.user_dep
								WHERE
									d.order_id = @orderId;
						end
					--根据ishaveedu来判断status的值是0（待审批）还是4（自动审批）
					set @status = 4;
					if @ishaveedu!=0
					begin
						set @status = 0;
					end
					--保存请假单
					insert into bd_leave_order(
						order_id,
						order_no,
						leave_id,
						start_date,
						end_date,
						creater_id,
						people_num,
						total_time,
						remark,
						apply_type,
						status
					)VALUES(
						@orderId,
						@orderNo,
						@leaveId,
						@startDate,
						@endDate,
						@operator,
						@count,
						@totalDuration*@count,
						@description,
						@applyType,
						@status
					)
					--保存请假明细
						insert into lr_kqingj(user_serial,bh,kssj,jssj,jsjg,gly_no,order_id)
						SELECT
							p.user_serial,
							d.leave_id,
							d.leave_date,
							d.leave_date,
							d.leave_minute,
							@operator,
							d.order_id
						FROM
							bd_temp_dayDetail d,
							#qualified_people p
						WHERE
							d.order_id = @orderId

					--遍历调用
					DECLARE userOfReset CURSOR LOCAL FOR SELECT user_serial FROM #qualified_people

					OPEN userOfReset FETCH NEXT FROM userOfReset INTO @user_serial
					WHILE @@fetch_status = 0
						BEGIN
								--重新计算
								exec bd_proc_reset_data @startDate,@ishaveedu,@user_serial,@operator

							FETCH NEXT FROM userOfReset INTO @user_serial
						END
				END

			--返回的数据
			SELECT
						user_no AS 'userNo',
						user_name AS 'userName',
						dept_name AS 'deptName',
						error_msg as 'errorMsg'
						from #return_table;

			 --计算考勤
      DECLARE @users VARCHAR(MAX) = ''

      SELECT
        @users = @users + CAST(u.user_serial AS VARCHAR(100)) + ','
      FROM #qualified_people u
      WHERE u.order_id = @orderId

			SET @users = SUBSTRING(@users,0, LEN(@users))
      EXEC bd_proc_timecard_extend @startDate, @endDate, @users,2

			delete from bd_temp_dayDetail where order_id = @orderId;
			delete from bd_temp_checkPeople where order_id = @orderId;

			DROP TABLE #return_table;
			DROP TABLE #qualified_people;
			commit tran
		end try
	begin catch
			DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
			IF XACT_STATE() <> 0
			BEGIN
			  ROLLBACK TRANSACTION;
			  RAISERROR (@errStr, 16, 1);
				CLOSE userOfLeave --关闭游标
				DEALLOCATE userOfLeave --释放游标
			END;
	end catch

END
go
